﻿Imports System.Data.SqlClient

Public Class FormCategory
    Public Sub RefreshData()
        DataGridView1.Rows.Clear()

        Dim cmd As New SqlCommand()
        cmd.Connection = SQL.connection()
        cmd.CommandText = "SELECT * FROM Category WHERE CategoryName LIKE N'%" & txtSearch.Text & "%' or CategoryId LIKE '%" & txtSearch.Text & "%'"
        Dim reader As SqlDataReader = cmd.ExecuteReader()

        While reader.Read
            DataGridView1.Rows.Add(reader.GetValue(0), reader.GetValue(1), reader.GetValue(2))
        End While
        reader.Close()
    End Sub

    Private Sub FormCategory_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.RefreshData()


    End Sub

    Private Sub txtSearch_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtSearch.TextChanged
    Me.RefreshData()
    End Sub

    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click

        Dim frm As New FormCategoryEditor()
        If frm.ShowDialog() = Windows.Forms.DialogResult.OK Then

               Me.RefreshData()
        End If

    End Sub

    Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click
        If Me.DataGridView1.SelectedRows.Count = 0 Then
            Exit Sub
        End If
        Dim row As DataGridViewRow = Me.DataGridView1.SelectedRows(0)
        Dim frm As New FormCategoryEditor()
        frm.txtCategoryId.Text = row.Cells("CategoryId").Value
        frm.txtCategoryName.Text = row.Cells("CategoryName").Value
        frm.txtTypeId.Text = row.Cells("TypeId").Value
        If frm.ShowDialog() = Windows.Forms.DialogResult.OK Then
                Me.RefreshData()

        End If
    End Sub

    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        If Me.DataGridView1.SelectedRows.Count = 0 Then
            Exit Sub
        End If

        If MessageBox.Show("Are you sure want to delete?", "Delete", MessageBoxButtons.YesNo) = Windows.Forms.DialogResult.No Then
            Exit Sub
        End If

        Dim row As DataGridViewRow = Me.DataGridView1.SelectedRows(0)

        Dim id = row.Cells("CategoryId").Value

        Dim cn As New SqlConnection
        cn.ConnectionString = "server=.;uid=sa;pwd=123;database=SaleDb"
        cn.Open()


        Dim cmd As New SqlCommand
        cmd.Connection = cn
        cmd.CommandText = "DELETE FROM Category WHERE CategoryId = " & id

        cmd.ExecuteNonQuery()

        Me.RefreshData()
    End Sub
End Class